

IF OBJECT_ID('dbo.RptProgressDeliverableMasterDetail') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptProgressDeliverableMasterDetail
    IF OBJECT_ID('dbo.RptProgressDeliverableMasterDetail') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptProgressDeliverableMasterDetail >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptProgressDeliverableMasterDetail >>>'
END
go

-- =======================================================================
-- Procedure Name: dbo.RptProgressDeliverableMasterDetail
--
-- Author:      Ponraja Chandran
--
-- Create date: 10/09/2009
--
-- Description: Gets Raw data of Financial Deliverables Summary
--
-- Serves as a drill down to the Report, RptProgressDeliverableMasterDetail
--
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
--exec RptProgressDeliverableMasterDetail '01/01/2009','06/01/2009',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'ORG',11055  
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 10/09/09    PC        Created based on RptProgressDeliverableMasterDetail.sql
--
-- =======================================================================
   
  
CREATE PROCEDURE dbo.RptProgressDeliverableMasterDetail    
     (      
     @StartDate                         DATETIME,        
     @EndDate                           DATETIME,           
     @DeliverableStatus                 VARCHAR(5000) = NULL,      
     @Institution                       VARCHAR(100) = NULL,      
     @Department                        VARCHAR(100) = NULL,      
     @Unit                              VARCHAR(100) = NULL,      
     @ChiefCode                         VARCHAR(4)   = NULL,      
     @FundNumber                        VARCHAR(1000) = NULL,      
     @PrincipalInvestigator             VARCHAR(100) = NULL,       
     @OriginatingSponsor                VARCHAR(100) = NULL,      
     @Sponsor                           VARCHAR(100) = NULL,           
     @SubcontractSpecialist             VARCHAR(100) = NULL,      
     @SelectedHeaderStatus              VARCHAR(1000)= NULL,      
     @DomainUserId                      INT      
     )       
WITH RECOMPILE                                                
AS      
BEGIN      
     -- Initial Settings      
     SET NOCOUNT ON      
      
      
     -- Declare Local Variables      
     DECLARE @Error              INT,      
    @RowCount           INT,       
    @DeliverableStatusCodes VARCHAR(5000),      
    @DeliverableType  VARCHAR(200),  
    @MonthDateFrom DATETIME,  
    @MonthDateTo   DATETIME  
  
     
                            
 declare @ReportDate DATETIME               
    set @ReportDate = convert(datetime, convert(varchar(11),getdate()))        
    --set @ReportDate = convert(datetime, convert(varchar(11),'11/1/2007'))   
    SET @DeliverableType = NULL      
      
     -- Need to break the filter out to multiple steps, so need a temp table here      
     DECLARE   @Proposals TABLE (      
                    FolderNumber             VARCHAR(12)    NULL,      
                    InfoEdPropNumber         VARCHAR(50)    NULL,      
                    Institution              VARCHAR(50)    NULL,      
                    Department               VARCHAR(50)    NULL,      
                    Unit                     VARCHAR(50)    NULL,      
                    ChiefCode                VARCHAR(4)     NULL,      
                    SponsorName              VARCHAR(75)    NULL,      
                    SponsorTypeName          VARCHAR(50)    NULL,      
                    SecondarySponsor         VARCHAR(75)    NULL,      
                    ProposalShortTitle       VARCHAR(64)    NULL,      
                    PrincipalInvestigator    VARCHAR(64)    NULL,      
                    ProjectStartDate         DATETIME       NULL,      
                    ProjectEndDate           DATETIME       NULL,      
                    InstrumentType           VARCHAR(50)    NULL,      
                    SubContractorName        VARCHAR(75)    NULL,      
                    BudgetProjectStartDate   DATETIME       NULL,      
					BudgetProjectEndDate     DATETIME       NULL,      
                    ProjectAwardedAmount     MONEY          NULL,      
                    BudgetStartDate          DATETIME       NULL,      
                    BudgetEndDate            DATETIME       NULL,      
                    BudgetFundNumber         VARCHAR(20)    NULL,      
                    ExpenseAmount            MONEY          NULL,      
                    IndirectExpenses         MONEY          NULL,      
                    DirectExpenses           MONEY          NULL,      
                    CashBalance              MONEY          NULL,      
                    ReportId                 BIGINT         NULL,      
                    ReportName               VARCHAR(50)    NULL,      
                    REPORTCAT                VARCHAR(80)    NULL,      
                    REPORTSTAT               VARCHAR(80)    NULL,      
                    SubmittedDate            DATETIME       NULL,      
                    DueDate                  DATETIME       NULL,      
                    StatusDate               DATETIME       NULL,      
                    --CompletedDate            DATETIME       NULL,      
                   -- DaysFromDueToCompleted   VARCHAR(5)     NULL,      
                    SubmittedBy              VARCHAR(50)    NULL,      
                    StatusBy                 VARCHAR(50)    NULL,      
                    --CompletedBy              VARCHAR(50)    NULL,      
                    PeriodStartDate          DATETIME       NULL,      
                    PeriodEndDate            DATETIME       NULL,      
                    DeliverableComments      VARCHAR(2000)  NULL,      
                    REPORTCAT_CODE           VARCHAR(50)    NULL,      
                    REPORTSTAT_CODE          VARCHAR(50)    NULL,      
                    AgreementId              INT            NULL,      
                    InstitutionId            INT            NULL,      
                    DepartmentId             INT            NULL,      
                    UnitId                   INT            NULL,      
                    SponsorId                INT            NULL,  
					SponsorAgreementID       VARCHAR(50)    NULL,    
                    PrincipalInvestigatorId  INT            NULL,      
                    SubcontractsYN           CHAR(1)        NULL,      
                    SubContractorId          INT            NULL,      
                    SubOutContractorId       INT            NULL )      
      
     -- ALL checking      
     IF isNull(@DeliverableStatus,'All') IN ('ALL','','0') SET @DeliverableStatus = 'All'      
     IF @Institution IN ('ALL','','0') SET @Institution = NULL      
     IF @Department IN ('ALL','','0') SET @Department = NULL      
     IF @Unit IN ('ALL','','0') SET @Unit = NULL      
     IF @ChiefCode IN ('ALL','','0') SET @ChiefCode = NULL      
     IF @FundNumber IN ('ALL','','0') SET @FundNumber = NULL      
     IF @PrincipalInvestigator IN ('ALL','','0') SET @PrincipalInvestigator = NULL      
     IF @OriginatingSponsor IN ('ALL','','0') SET @OriginatingSponsor = NULL      
     IF @Sponsor IN ('ALL','','0') SET @Sponsor = NULL      
     --IF @Subcontractor IN ('ALL','','0') SET @Subcontractor = NULL      
     IF @SubcontractSpecialist IN ('ALL','','0') SET @SubcontractSpecialist = NULL      
           
     -- Verify firt.      
     IF @DeliverableType IS NOT NULL AND @DeliverableType NOT IN ('ALL','Progress')      
     BEGIN      
          RAISERROR ('ERROR! DeliverableType has an invalid value(%s). Set to a valid DeliverableType and try again.',11,1,@DeliverableType)       
          RETURN -1      
     END      
                     
     -- Set Deliverable Type      
     IF @DeliverableType IS NOT NULL      
          SELECT @DeliverableType = CASE @DeliverableType       
                              WHEN 'Progress' THEN 'REPORTCAT_48'      
                              ELSE 'REPORTCAT_48'   -- ALL      
                           END      
      
     -- Check to see if All is anywhere in the status string      
 IF @DeliverableStatus like '%,All%' OR @DeliverableStatus like '%All,%'      
 BEGIN      
  SELECT @DeliverableStatus = 'All'      
 END      
      
      -- If drilling down to a single status, set the codes here      
 SELECT    @DeliverableStatusCodes = 'ERROR'      
      
      
   
  SELECT    @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Awaiting%' THEN @DeliverableStatusCodes + ',REPORTSTAT_43' ELSE @DeliverableStatusCodes END      
  SELECT    @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Pending%' THEN @DeliverableStatusCodes + ',REPORTSTAT_45' ELSE @DeliverableStatusCodes END     
  SELECT    @DeliverableStatusCodes = CASE WHEN isNull(@DeliverableStatus,'All') = 'All' THEN 'REPORTSTAT_43,REPORTSTAT_45' ELSE @DeliverableStatusCodes END      
      
      
     --Remove error string if other values were added to string      
     SELECT @DeliverableStatusCodes = replace(@DeliverableStatusCodes,'ERROR,','')      
         
     --Check for Errors      
     IF @DeliverableStatusCodes = 'ERROR' and isNull(@DeliverableStatus,'All') <> 'All'      
     BEGIN      
          RAISERROR ('ERROR! DeliverableStatus has an invalid value. Set to a valid StatusColumn name.',11,1,@DeliverableStatus)       
          RETURN -1      
     END      
      
     -- Performance Issues, Get Secured AgreementIds into a temp table      
     CREATE TABLE   #tt_Agreements (AgreementId INT)      
           
     INSERT    #tt_Agreements (AgreementId)      
     SELECT    AgreementId      
     FROM      dbo.fnGetSecurityAgreements(@DomainUserId)       
           
          
     -- Result Set into temp table      
     INSERT    @Proposals      
                    (FolderNumber,      
                    InfoEdPropNumber,      
                    Institution,      
                    Department,      
                    Unit,      
                    ChiefCode,      
                    SponsorName,      
                    SponsorTypeName,      
                    SecondarySponsor,      
                    ProposalShortTitle,      
                    PrincipalInvestigator,      
                    ProjectStartDate,      
                    ProjectEndDate,      
                    InstrumentType,      
                    SubContractorName,      
                    BudgetProjectStartDate,      
                    BudgetProjectEndDate,      
                    ProjectAwardedAmount,      
                    BudgetStartDate,      
                    BudgetEndDate,      
                    BudgetFundNumber,      
                    ExpenseAmount,      
                    IndirectExpenses,      
                    DirectExpenses,      
                    CashBalance,      
                    ReportId,      
                    ReportName,      
                    REPORTCAT,      
                    REPORTSTAT,      
                    SubmittedDate,      
                    DueDate,      
                    StatusDate,      
                    --CompletedDate,      
                    --DaysFromDueToCompleted,      
                    SubmittedBy,      
                    StatusBy,      
                    --CompletedBy,      
                    PeriodStartDate,      
                    PeriodEndDate,      
                    DeliverableComments,      
                    REPORTCAT_CODE,      
                    REPORTSTAT_CODE,      
                    AgreementId,                      
					InstitutionId,      
                    DepartmentId,      
                    UnitId,      
                    SponsorId,      
					SponsorAgreementID,
                    PrincipalInvestigatorId,      
                    SubcontractsYN,      
                    SubContractorId,      
                    SubOutContractorId)      
     SELECT    AI.FolderNumber,      
               AI.InfoEdPropNumber,      
               AI.Institution,      
               AI.Department,      
               AI.Unit,      
               AI.ChiefCode,      
               AI.SponsorName,      
               AI.SponsorTypeName,      
               AI.SecondarySponsor,      
               AI.ProposalShortTitle,      
               AI.PrincipalInvestigator,      
               AI.ProjectStartDate,      
               AI.ProjectEndDate,      
               AI.InstrumentType,                                      
               CASE WHEN DEL.REPORTCAT_CODE IN ('REPORTCAT_13','REPORTCAT_40','REPORTCAT_43')       
                    THEN 'N/A'                -- In      
                    ELSE dbo.fnParseSubcontractorName(DEL.ReportName)          -- Out      
               END AS SubContractorName,      
               BUDG.ProjectStartDate AS BudgetProjectStartDate,      
               BUDG.ProjectEndDate AS BudgetProjectEndDate,      
               BUDG.ProjectAwardedAmount,      
               BUDG.BudgetStartDate,      
               BUDG.BudgetEndDate,      
               BUDG.FundNumber AS BudgetFundNumber,      
               BUDG.ExpenseAmount,      
               BUDG.IndirectExpenses,      
               BUDG.DirectExpenses,      
               -1 * isNull(BUDG.CashBalance,0),      
               DEL.ReportId,      
               DEL.ReportName,      
               DEL.REPORTCAT,      
               DEL.REPORTSTAT,      
               DEL.SubmittedDate,      
               DEL.DueDate,      
               DEL.StatusDate,      
               --DEL.CompletedDate,      
			   --DEL.DaysComplCalc AS DaysFromDueToCompleted,      
               ISNULL(DEL.SubmittedBy,'UNASSIGNED') AS Submittedby,      
               DEL.StatusBy,      
               --DEL.CompletedBy,      
               DEL.PeriodStartDate,      
               DEL.PeriodEndDate,      
               DEL.Comments AS DeliverableComments,      
               DEL.REPORTCAT_CODE,      
               DEL.REPORTSTAT_CODE,      
               AI.AgreementId,      
               AI.InstitutionId,      
               AI.DepartmentId,      
               AI.UnitId,      
               AI.SponsorId,  
			   AI.SponsorAgreementID,    
               AI.PrincipalInvestigatorId,      
               AI.SubcontractsYN,      
               AI.SubContractorId,      
               NULL      
                     
     FROM      dbo.AgrAgreement_Info AI       
                     
               -- Security Issues: Not all Users can see all Agreements!      
               INNER JOIN      
                    #tt_Agreements AUTH ON AI.AgreementId = AUTH.AgreementId      
                          
               INNER JOIN      
                    (SELECT D.prop_no,      
                            D.ReportId,      
                            D.ReportName,      
                            D.REPORTCAT_CODE,      
                            D.REPORTCAT,      
                            D.REPORTSTAT,      
                            D.REPORTSTAT_CODE,      
                            D.DueDate,      
                            D.SubmittedDate,      
                            D.ProcessedDate AS StatusDate,      
                            --D.CompletedDate,      
                            D.SubmittedBy,      
                            D.StatusBy,      
                            --D.CompletedBy,      
                            D.PeriodStartDate,      
                            D.PeriodEndDate,      
                            D.Comments      
                            --D.DaysComplCalc      
                    FROM    dbo.vwInfoEdProgressSummaryReport D      
                            INNER JOIN (SELECT  ReportId,      
                                                MAX(ProcessedDate) AS LastDate      
                                        FROM    dbo.vwInfoEdProgressSummaryReport      
                                        GROUP BY ReportId) LAST ON D.ReportId = LAST.ReportId AND       
                            D.ProcessedDate = LAST.LastDate ) DEL ON AI.InfoEdPropNumber = DEL.prop_no      
                     
               LEFT OUTER JOIN      
                    -- This function uses the Agreement_Fct table to get Budget and Project data      
                    dbo.fnGetFactTableBudget(@StartDate, @EndDate) BUDG ON AI.AgreementId = BUDG.AgreementId      
                     
     WHERE      (DEL.DueDate BETWEEN @StartDate AND @EndDate) AND      
                (@DeliverableType IS NULL OR DEL.REPORTCAT_CODE IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@DeliverableType))) AND      
                (DEL.REPORTSTAT_CODE IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@DeliverableStatusCodes))) AND      
                (@Institution IS NULL OR AI.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution))) AND      
                (@Department IS NULL OR ISNULL(AI.DepartmentId,-1) IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Department))) AND      
                (@Unit IS NULL OR AI.UnitId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Unit))) AND      
                (@ChiefCode IS NULL OR AI.ChiefCode IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@ChiefCode))) AND                     
                (@PrincipalInvestigator IS NULL OR AI.PrincipalInvestigatorId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PrincipalInvestigator ))) AND      
                (@Sponsor IS NULL OR AI.SponsorId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @Sponsor )))       
                     
     ORDER BY  AI.Institution,      
               AI.Department,   
               AI.Unit,      
               AI.FolderNumber,      
               DEL.StatusDate      
                    
      
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT      
     IF @Error!=0      
     BEGIN      
          RAISERROR ('ERROR! Getting Intial Agreement list into TempTable, @Proposals',11,1)      
          DROP TABLE #tt_Agreements      
          RETURN -1      
     END      
           
     -- Even on Success. Drop now.      
     DROP TABLE #tt_Agreements      
           
     -- Return Result Set using additional params      
     SELECT    FolderNumber,      
               InfoEdPropNumber,      
               Institution,      
               Department,      
               Unit,      
               ChiefCode,      
               SponsorName,      
               SponsorTypeName,      
               SecondarySponsor,      
               ProposalShortTitle,      
               PrincipalInvestigator,      
               ProjectStartDate,      
               ProjectEndDate,      
               InstrumentType,      
               SubContractorName,      
               BudgetProjectStartDate,      
               BudgetProjectEndDate,      
               ProjectAwardedAmount,      
               BudgetStartDate,      
               BudgetEndDate,      
               BudgetFundNumber,      
               ExpenseAmount,      
               IndirectExpenses as DirectExpenses,      
               DirectExpenses as IndirectExpenses,      
               CashBalance,      
               ReportId,      
               ReportName,      
               REPORTCAT,      
               CASE     
			   WHEN REPORTSTAT_CODE IN ('REPORTSTAT_43') THEN 1--1'Awaiting'                          
			   WHEN REPORTSTAT_CODE IN ('REPORTSTAT_45') THEN 2--'Pending'                          
			   ELSE NULL    
			   END AS REPORTSTATID,  
			   CASE     
			   WHEN REPORTSTAT_CODE IN ('REPORTSTAT_43') THEN 'Awaiting Departmental Paperwork' --'Awaiting'                          
			   WHEN REPORTSTAT_CODE IN ('REPORTSTAT_45') THEN 'Pending Out Years'  -- 'Pending'                          
			   ELSE NULL    
               END AS REPORTSTAT,        
               SubmittedDate,      
               --DueDate,      
               StatusDate,      
               --CompletedDate,      
               --DaysFromDueToCompleted,      
               SubmittedBy,      
               StatusBy,      
               --CompletedBy,      
               PeriodStartDate,      
               PeriodEndDate,      
               DeliverableComments,      
               REPORTCAT_CODE,      
               REPORTSTAT_CODE,      
               AgreementId,      
               InstitutionId,      
               DepartmentId,      
               UnitId,      
               SponsorId,
               SponsorAgreementID,      
               PrincipalInvestigatorId,      
               SubcontractsYN,      
               SubContractorId,      
               SubOutContractorId,  
			 COUNT( CASE WHEN DueDate between dateadd(mm,-1,@ReportDate) and dateadd(dd, -1,dateadd(mm,0,@ReportDate))       
			 THEN   DueDate      
			  ELSE NULL      
			  END )AS PAST30DAYS,         
			 COUNT( CASE WHEN DueDate between dateadd(mm,-2,@ReportDate) and dateadd(dd, -1,dateadd(mm,-1,@ReportDate))     
			 THEN  DueDate      
			  ELSE NULL      
			  END )AS PAST60DAYS ,      
			 COUNT( CASE WHEN DueDate between dateadd(mm,-3,@ReportDate) and dateadd(dd, -1,dateadd(mm,-2,@ReportDate))       
			 THEN  DueDate      
			  ELSE NULL      
			  END )AS PAST90DAYS ,   
			 COUNT( CASE WHEN DueDate between @StartDate  and dateadd(dd, -1,dateadd(mm,-3,@ReportDate))    
			 THEN  DueDate      
			  ELSE NULL      
			  END )AS PAST90PDAYS ,   
			 COUNT( CASE WHEN DueDate between dateadd(mm,0,@ReportDate) and dateadd(dd, -1,dateadd(mm,1,@ReportDate))       
			 THEN  DueDate      
			  ELSE NULL      
			  END )AS FUT30DAYS ,        
			 COUNT( CASE WHEN DueDate between dateadd(mm,1,@ReportDate) and dateadd(dd, -1,dateadd(mm,2,@ReportDate))       
			 THEN DueDate      
			  ELSE NULL      
			  END )AS FUT60DAYS,      
		   COUNT( CASE WHEN DueDate between dateadd(mm,2,@ReportDate) and dateadd(dd, -1,dateadd(mm,3,@ReportDate))       
			 THEN  DueDate      
			  ELSE NULL      
			  END )AS FUT90DAYS,
		   COUNT( CASE WHEN DueDate between dateadd(mm,3,@ReportDate) and @EndDate       
			 THEN  DueDate      
			  ELSE NULL      
			  END )AS FUT90PDAYS      
			 FROM      @Proposals      
     WHERE     (@FundNumber IS NULL OR BudgetFundNumber IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@FundNumber))) AND      
               (@OriginatingSponsor IS NULL OR SecondarySponsor = @OriginatingSponsor ) AND   
               --(DueDate BETWEEN @MonthDateFrom AND @MonthDateTo) AND     
                -- If user is drilling down, this would be passed...      
                --(@Subcontractor IS NULL OR SubContractorName = @SubContractor) AND      
               (@SubcontractSpecialist IS NULL OR SubmittedBy = @SubcontractSpecialist OR      
               (@SubcontractSpecialist = 'UNASSIGNED' AND SubmittedBy IS NULL ))    
  
     GROUP BY   
			   FolderNumber,      
               InfoEdPropNumber,      
               Institution,      
               Department,      
               Unit,      
               ChiefCode,      
               SponsorName,      
               SponsorTypeName,      
               SecondarySponsor,      
               ProposalShortTitle,      
               PrincipalInvestigator,      
               ProjectStartDate,      
               ProjectEndDate,      
               InstrumentType,      
               SubContractorName,      
               BudgetProjectStartDate,      
               BudgetProjectEndDate,      
               ProjectAwardedAmount,      
               BudgetStartDate,      
               BudgetEndDate,      
               BudgetFundNumber,      
               ExpenseAmount,      
               DirectExpenses,      
               IndirectExpenses,      
               CashBalance,      
               ReportId,      
               ReportName,      
               REPORTCAT,   
               REPORTSTAT,        
               SubmittedDate,  
               StatusDate,      
               --CompletedDate,      
               --DaysFromDueToCompleted,      
               SubmittedBy,      
               StatusBy,      
               --CompletedBy,      
               PeriodStartDate,      
               PeriodEndDate,      
               DeliverableComments,      
               REPORTCAT_CODE,      
               REPORTSTAT_CODE,      
               AgreementId,      
               InstitutionId,      
               DepartmentId,      
               UnitId,      
               SponsorId,    
			   SponsorAgreementID,
               PrincipalInvestigatorId,      
               SubcontractsYN,      
               SubContractorId,      
               SubOutContractorId  
     
           
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT      
     IF @Error!=0      
     BEGIN      
          RETURN -1      
     END  
     -- Return Success      
     RETURN 0      
           
END   
  

go

IF OBJECT_ID('dbo.RptProgressDeliverableMasterDetail') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptProgressDeliverableMasterDetail >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptProgressDeliverableMasterDetail >>>'
go

GRANT EXECUTE ON dbo.RptProgressDeliverableMasterDetail TO InsightReport,Insight
go